﻿#region << 版 本 注 释 >>
/*----------------------------------------------------------------
 * 修改人：x
 * 时间：
 * 修改说明：
 *----------------------------------------------------------------*/
#endregion << 版 本 注 释 >>using System;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using seejee_Backendium.Core.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.Json.Serialization;
using System.Threading.Tasks;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace seejee_Backendium.Data.Repositores
{
    public class StoreRepository
    {
        private readonly string _connectionString;
        private readonly IConfiguration _configuration;

        public StoreRepository(string connectionString, IConfiguration configuration)
        {
            _connectionString = connectionString;
            _configuration = configuration;
        }

        //获取所有用户
        //public PagedResult<StoreInfoDTO> GetAllStores(int pageIndex, int pageSize, string keyword)
        //{
        //    var stores = new List<StoreInfoDTO>();
        //    int totalCount = 0;
        //    // 从配置读取连接字符串
        //    string connStr = _configuration.GetConnectionString("Default");

        //    using (SqlConnection connection = new SqlConnection(connStr))
        //    {
        //        connection.Open();
        //        // 获取总数
        //        string countSql = "SELECT COUNT(*) FROM SEBI_StoreDefinitions WHERE cStoreName = '' OR cStoreName LIKE '%' + @keyword + '%'";
        //        using (SqlCommand countCmd = new SqlCommand(countSql, connection))
        //        {
        //            countCmd.Parameters.AddWithValue("@keyword", keyword);
        //            totalCount = (int)countCmd.ExecuteScalar();
        //        }
        //        string query = "select ID,cStoreName,cStoredProcedures,cComment,dCreateTime from SEBI_StoreDefinitions where cStoreName = '' OR cStoreName LIKE '%' + @keyword + '%'  ORDER BY cStoreName OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;;";
        //        using (SqlCommand command = new SqlCommand(query, connection))
        //        {
        //            command.Parameters.AddWithValue("@Offset", (pageIndex - 1) * pageSize);
        //            command.Parameters.AddWithValue("@PageSize", pageSize);
        //            command.Parameters.AddWithValue("@keyword", keyword);

        //            using (SqlDataReader reader = command.ExecuteReader())
        //            {
        //                while (reader.Read())
        //                {
        //                    stores.Add(new StoreInfoDTO
        //                    {
        //                        ID = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
        //                        cStoreName = reader.IsDBNull(1) ? "" : reader.GetString(1),
        //                        cStoredProcedures = reader.IsDBNull(2) ? "" : reader.GetString(2),
        //                        cComment = reader.IsDBNull(3) ? "" : reader.GetString(3),
        //                        dCreateTime = reader.GetDateTime(4),
        //                    });
        //                    Console.WriteLine(stores);
        //                }
        //            }
        //        }
        //    }
        //    return new PagedResult<StoreInfoDTO>
        //    {
        //        Items = stores,
        //        TotalCount = totalCount
        //    };
        //}
        public PagedResult<StoreInfoDTO> GetAllStores(int pageIndex, int pageSize, string keyword)
        {
            var stores = new List<StoreInfoDTO>();
            int totalCount = 0;
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();

                // 修改计数查询，添加表连接以确保计数准确性
                string countSql = @"
            SELECT COUNT(*) 
            FROM SEBI_StoreDefinitions s
            LEFT JOIN CusUserList c ON s.iCusID = c.iCusID
            WHERE  (s.cStoreName = '' OR s.cStoreName LIKE '%' + @keyword + '%')"; 
                using (SqlCommand countCmd = new SqlCommand(countSql, connection))
                {
                    countCmd.Parameters.AddWithValue("@keyword", keyword);
                    totalCount = (int)countCmd.ExecuteScalar();
                }

                // 修改数据查询，添加表连接并获取cCusName
                string query = @"
            SELECT 
                s.ID, 
                s.cStoreName, 
                s.cStoredProcedures, 
                s.cComment, 
                s.dCreateTime,
                ISNULL(c.cCusName, '') AS cCusName 
            FROM SEBI_StoreDefinitions s
            LEFT JOIN CusUserList c ON s.iCusID = c.iCusID 
            WHERE (s.cStoreName = '' OR s.cStoreName LIKE '%' + @keyword + '%')

            ORDER BY  c.cCusName 
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;;";

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Offset", (pageIndex - 1) * pageSize);
                    command.Parameters.AddWithValue("@PageSize", pageSize);
                    command.Parameters.AddWithValue("@keyword", keyword);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            stores.Add(new StoreInfoDTO
                            {
                                ID = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                                cStoreName = reader.IsDBNull(1) ? "" : reader.GetString(1),
                                cStoredProcedures = reader.IsDBNull(2) ? "" : reader.GetString(2),
                                cComment = reader.IsDBNull(3) ? "" : reader.GetString(3),
                                dCreateTime = reader.GetDateTime(4)
                            });
                        }
                    }
                }
            }

            return new PagedResult<StoreInfoDTO>
            {
                Items = stores,
                TotalCount = totalCount
            };
        }


        //获取存储名
        public async Task<IEnumerable<StoreInfoDTO>> GetStoreName()
        {
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                await connection.OpenAsync();

                string sql = "select cStoreName from SEBI_StoreDefinitions";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    var storeNames = new List<StoreInfoDTO>();

                    using (SqlDataReader reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            string storeName = reader.IsDBNull(0) ? "" : reader.GetString(0);
                            if (!string.IsNullOrEmpty(storeName))
                            {
                                storeNames.Add(new StoreInfoDTO
                                {
                                    cStoreName = storeName
                                });
                            }
                        }
                    }

                    return storeNames;
                }
            }

        }

        //获取菜单名
        //public async Task<IEnumerable<string[]>> GetViewName(string cCusName)
        //{
        //    string connStr = _configuration.GetConnectionString("Default");
        //    // 创建存储结果的列表
        //    var viewNameArrays = new List<string[]>();
        //    using (SqlConnection connection = new SqlConnection(connStr))
        //    {
        //        await connection.OpenAsync();

        //        string sql = "select iCusBIPermissions from CusUserList where cCusName = @cCusName";

        //        using (SqlCommand command = new SqlCommand(sql, connection))
        //        {
        //            // 添加参数防止SQL注入
        //            command.Parameters.AddWithValue("@cCusName", cCusName);

        //            using (SqlDataReader reader = await command.ExecuteReaderAsync())
        //            {
        //                while (await reader.ReadAsync())
        //                {
        //                    string viewNames = reader.GetString(0);
        //                    // 读取vViewName字段的值，处理可能的DBNull
        //                    string[] viewNameArray = viewNames.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
        //                    // 只添加非空值
        //                    if (viewNameArray.Length > 0)
        //                    {
        //                        viewNameArrays.Add(viewNameArray);
        //                    }
        //                }
        //            }
        //        }
        //    }
        //    return viewNameArrays;

        //}
        //public async Task<IEnumerable<string>> GetViewName(string cCusName)
        //{
        //    string connStr = _configuration.GetConnectionString("Default");
        //    // 创建存储结果的列表
        //    var viewNames = new List<string>();
        //    using (SqlConnection connection = new SqlConnection(connStr))
        //    {
        //        await connection.OpenAsync();

        //        string sql = "select cMenuName from SEBI_MenuConfig where cCusName = @cCusName";

        //        using (SqlCommand command = new SqlCommand(sql, connection))
        //        {
        //            // 添加参数防止SQL注入
        //            command.Parameters.AddWithValue("@cCusName", cCusName);

        //            using (SqlDataReader reader = await command.ExecuteReaderAsync())
        //            {
        //                while (await reader.ReadAsync())
        //                {
        //                    // 读取vViewName字段的值，处理可能的DBNull
        //                    string viewName = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
        //                    // 只添加非空值
        //                    if (!string.IsNullOrEmpty(viewName))
        //                    {
        //                        viewNames.Add(viewName);
        //                    }
        //                }
        //            }
        //        }
        //    }
        //    return viewNames;

        //}


        //查询存储过程详细内容
        public async Task<IEnumerable<StoreInfoDTO>> GetStore(string cStoreName)
        {
            var stores = new List<StoreInfoDTO>();
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                await connection.OpenAsync();

                string sql = "select cStoredProcedures,cComment from SEBI_StoreDefinitions where cStoreName = @cStoreName ";

                //using (SqlCommand command = new SqlCommand(sql, connection))
                //{
                //    command.Parameters.AddWithValue("@cStoreName", cStoreName);
                //    var storeNames = new List<StoreInfoDTO>();

                //    using (SqlDataReader reader = await command.ExecuteReaderAsync())
                //    {
                //        while (await reader.ReadAsync())
                //        {
                //            string storeName = reader.IsDBNull(0) ? "" : reader.GetString(0);
                //            if (!string.IsNullOrEmpty(storeName))
                //            {
                //                storeNames.Add(new StoreInfoDTO
                //                {
                //                    cStoreName = storeName
                //                });
                //            }
                //        }
                //    }

                //    return storeNames;
                //}
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@cStoreName", cStoreName);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            stores.Add(new StoreInfoDTO
                            {
                                cStoredProcedures = reader.IsDBNull(0) ? "" : reader.GetString(0),
                                cComment = reader.IsDBNull(1) ? "" : reader.GetString(1),
                            });
                            Console.WriteLine(stores);
                        }
                    }
                    return stores;
                }
            }

        }




        ////新增存储过程
        //public bool InsertStore(StoreInsertDTO stores)
        //{
        //    string connStr = _configuration.GetConnectionString("Default");
        //    using (SqlConnection conn = new SqlConnection(connStr))

        //    {
        //        conn.Open();
        //        // 开启事务 
        //        using (SqlTransaction transaction = conn.BeginTransaction())
        //        {
        //            try
        //            {
        //                // 插入主表 (Stores)
        //                string insertMainSql = @"insert into SEBI_StoreDefinitions(cStoreName,cStoredProcedures,cComment,dCreateTime) values (@cStoreName,@cStoredProcedures,@cComment,@dCreateTime); ";
        //                using (SqlCommand cmd = new SqlCommand(insertMainSql, conn, transaction))
        //                {
        //                    cmd.Parameters.AddWithValue("@cStoreName", stores.cStoreName ?? "");
        //                    cmd.Parameters.AddWithValue("@cStoredProcedures", stores.cStoredProcedures ?? "");
        //                    cmd.Parameters.AddWithValue("@cComment", stores.cComment ?? "");
        //                    cmd.Parameters.AddWithValue("@dCreateTime", stores.dCreateTime == default ? (object)DBNull.Value : stores.dCreateTime);
        //                }
        //                // 提交事务
        //                transaction.Commit();
        //                return true;
        //            }
        //            catch
        //            {
        //                transaction.Rollback();
        //                throw;
        //            }
        //        }
        //    }
        //}
        //新增存储过程
        public bool InsertStore(StoreInsertDTO stores)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                // 开启事务 
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 插入主表 (Stores)
                        string insertMainSql = @"
                            INSERT INTO SEBI_StoreDefinitions 
                            (cStoreName, cStoredProcedures, cComment, dCreateTime) 
                            VALUES (@cStoreName, @cStoredProcedures, @cComment, @dCreateTime);
                        ";
                        using (SqlCommand cmd = new SqlCommand(insertMainSql, conn, transaction))
                        {
                            // 添加参数
                            cmd.Parameters.AddWithValue("@cStoreName", stores.cStoreName ?? "");
                            cmd.Parameters.AddWithValue("@cStoredProcedures", stores.cStoredProcedures ?? "");
                            cmd.Parameters.AddWithValue("@cComment", stores.cComment ?? "");
                            cmd.Parameters.AddWithValue("@dCreateTime", stores.dCreateTime == default ? (object)DBNull.Value : stores.dCreateTime);



                            // 关键：执行SQL命令（缺少的步骤）
                            int rowsAffected = cmd.ExecuteNonQuery(); 
                            // 验证是否插入成功（影响行数 >=1）
                            if (rowsAffected <= 0)
                            {
                                throw new Exception("插入数据失败，未影响任何行");
                            }
                        }
                        // 提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        // 输出异常信息（便于调试）
                        Console.WriteLine($"插入失败：{ex.Message}");
                        throw; // 抛出异常让上层处理
                    }
                }
            }


        }
        //新增菜单
        //public bool InsertMenu(MenuInsertDTO menus)
        //{
        //    string connStr = _configuration.GetConnectionString("Default");
        //    using (SqlConnection conn = new SqlConnection(connStr))
        //    {
        //        conn.Open();
        //        // 开启事务 
        //        using (SqlTransaction transaction = conn.BeginTransaction())
        //        {
        //            try
        //            {
        //                // 插入主表 (menus)
        //                string insertMainSql = @"
        //                     INSERT INTO SEBI_MenuConfig
        //                     (cMenuName, cCusName, dCreateTime) 
        //                     VALUES (@cMenuName, @cCusName, @dCreateTime); SELECT SCOPE_IDENTITY() ";
        //                int newId = 0;
        //                using (SqlCommand cmd = new SqlCommand(insertMainSql, conn, transaction))
        //                {
        //                    // 添加参数
        //                    cmd.Parameters.AddWithValue("@cMenuName", menus.cMenuName ?? "");
        //                    cmd.Parameters.AddWithValue("@cCusName", menus.cCusName ?? "");
        //                    cmd.Parameters.AddWithValue("@dCreateTime", menus.dCreateTime == default ? (object)DBNull.Value : menus.dCreateTime);


        //                    // 关键：执行SQL命令（缺少的步骤）
        //                    int rowsAffected = cmd.ExecuteNonQuery();
        //                    // 验证是否插入成功（影响行数 >=1）
        //                    if (rowsAffected <= 0)
        //                    {
        //                        throw new Exception("插入数据失败，未影响任何行");
        //                    }
        //                }
        //                // 提交事务
        //                transaction.Commit();
        //                return true;
        //            }
        //            catch (Exception ex)
        //            {
        //                transaction.Rollback();
        //                // 输出异常信息（便于调试）
        //                Console.WriteLine($"插入失败：{ex.Message}");
        //                throw; // 抛出异常让上层处理
        //            }
        //        }
        //    }
        //}
 

        //修改存储过程
        public bool UpdateStore(StoreInsertDTO store)
        {
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                // 开启事务 
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 更新主表 (Stores)
                        string updateMainSql = @"
                        UPDATE SEBI_StoreDefinitions
                        SET cStoredProcedures = @cStoredProcedures, 
                            cComment = @cComment
                        WHERE cStoreName = @cStoreName";

                        using (SqlCommand cmd = new SqlCommand(updateMainSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@cStoreName", store.cStoreName ?? "");
                            cmd.Parameters.AddWithValue("@cStoredProcedures", store.cStoredProcedures ?? "");
                            cmd.Parameters.AddWithValue("@cComment", store.cComment ?? "");

                            int mainRows = cmd.ExecuteNonQuery();
                            if (mainRows == 0) throw new Exception("存储过程更新失败");
                        }
                        // 提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }

        }
 

        //删除存储过程
        public bool DeleteStore(string cStoreName)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 先删除密钥表
                        string deleteLicensesSql = "DELETE FROM SEBI_StoreDefinitions WHERE cStoreName = @cStoreName";
                        using (SqlCommand cmd = new SqlCommand(deleteLicensesSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@cStoreName", cStoreName);
                            cmd.ExecuteNonQuery();
                        }

                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

    }
}
